﻿'Description:事务管理相关
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-09-06
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class TransactionBusiness
    ''' <summary>
    ''' 查询事务数据--分页
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTransaction(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql As New StringBuilder
        strSql.AppendLine(<string>
                        SELECT TransactionID,TransactionType,TransactionCategory,DECODE(TransactionType,'0','领导交办','1','会议纪要','其他') TransactionTypeSTATUS,TransactionContent,HandleItem,MainUnit,MainEmployeeID,
                        CoordinateUnit,CoordinateEmployeeID,to_char(RequiredFinishDate,'yyyy-MM-dd') RequiredFinishDate,Deliverables,DECODE(Status,0,'待接收','1','进行中','2','已完成','3','未完成','') Status,CreateEmployeeID,
                        to_char(CreateDate,'yyyy-MM-dd hh24:mi:ss') CreateDate,RecieveEmployeeID,to_char(RecieveDate,'yyyy-MM-dd hh24:mi:ss') RecieveDate,to_char(ActualFinishDate,'yyyy-MM-dd hh24:mi:ss') ActualFinishDate,
                        e.FullNAME MainEmployee,e2.FullNAME CoordinateEmployee,e3.FullNAME CreateEmployee,e4.FULLNAME RECIEVEEMPLOYEE
                        FROM ZSTransactionInfo t 
                        LEFT JOIN EMPLOYEE e ON e.EmployeeID=t.MainEmployeeID
                        LEFT JOIN EMPLOYEE e2 ON e2.EmployeeID=t.CoordinateEmployeeID
                        LEFT JOIN EMPLOYEE e3 ON e3.EmployeeID=t.CreateEmployeeID
                        LEFT JOIN EMPLOYEE e4 ON e4.EMPLOYEEID =t.RECIEVEEMPLOYEEID 
                        WHERE 1=1
                          </string>)
        If para.ContainsKey("TransactionType") Then
            If Not String.IsNullOrWhiteSpace(para("TransactionType")) Then
                strSql.AppendFormat(" and TransactionType='{0}' ", para("TransactionType"))
            End If
        End If
        If para.ContainsKey("TransactionCategory") Then
            If Not String.IsNullOrWhiteSpace(para("TransactionCategory")) Then
                strSql.AppendFormat(" and TransactionCategory='{0}' ", para("TransactionCategory"))
            End If
        End If
        If para.ContainsKey("MainUnit") Then
            If Not String.IsNullOrWhiteSpace(para("MainUnit")) Then
                strSql.AppendFormat(" and MainUnit like '%{0}%' ", para("MainUnit"))
            End If
        End If
        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                strSql.AppendFormat(" and Status='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                strSql.AppendFormat(" and to_char(RequiredFinishDate,'yyyy-MM-dd')>='{0}' ", para("startedate"))
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                strSql.AppendFormat(" and to_char(RequiredFinishDate,'yyyy-MM-dd')<='{0}' ", para("enddate"))
            End If
        End If
        strSql.AppendLine(" order by t.CreateDate DESC")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 插入事务
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertTransaction(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim strSql As New StringBuilder
        strSql.AppendLine(<string>INSERT INTO ZSTransactionInfo(TRANSACTIONID,TRANSACTIONTYPE,TRANSACTIONCONTENT,HANDLEITEM,MAINUNIT,
                            MAINEMPLOYEEID,COORDINATEUNIT,COORDINATEEMPLOYEEID,REQUIREDFINISHDATE,DELIVERABLES,STATUS,CREATEEMPLOYEEID,TransactionCategory,CREATEDATE)</string>)
        strSql.AppendFormat("VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',to_date('{8}','yyyy-MM-dd'),'{9}','{10}','{11}','{12}',sysdate)",
                            para("TRANSACTIONID"), para("TRANSACTIONTYPE"), para("TRANSACTIONCONTENT"),
                            para("HANDLEITEM"), para("MAINUNIT"), para("MAINEMPLOYEEID"), para("COORDINATEUNIT"), para("COORDINATEEMPLOYEEID"), para("REQUIREDFINISHDATE"), para("DELIVERABLES"),
                            "0", para("CREATEEMPLOYEEID"), para("TRANSACTIONCATEGORY"))
        Return DatabaseHelper.ExecuteSql(strSql.ToString) > 0
    End Function
    ''' <summary>
    ''' 删除事务
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteTransaction(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim strSql As New StringBuilder
        strSql.AppendFormat("DELETE FROM ZSTransactionInfo WHERE TRANSACTIONID IN('{0}')", para("IDS").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(strSql.ToString) > 0
    End Function
    ''' <summary>
    ''' 更新事务
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateTransaction(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        sb.AppendLine("UPDATE ZSTransactionInfo SET ")
        If para.ContainsKey("CREATEEMPLOYEEID") Then '保存更新
            sb.AppendFormat("TRANSACTIONTYPE='{0}',TRANSACTIONCONTENT='{1}',HANDLEITEM='{2}',MAINUNIT='{3}',MAINEMPLOYEEID='{4}',COORDINATEUNIT='{5}',COORDINATEEMPLOYEEID='{6}',REQUIREDFINISHDATE=to_date('{7}','yyyy-MM-dd'),DELIVERABLES='{8}',TRANSACTIONCATEGORY='{9}',CREATEDATE=sysdate",
                            para("TRANSACTIONTYPE"), para("TRANSACTIONCONTENT"), para("HANDLEITEM"), para("MAINUNIT"), para("MAINEMPLOYEEID"),
                            para("COORDINATEUNIT"), para("COORDINATEEMPLOYEEID"), para("REQUIREDFINISHDATE"), para("DELIVERABLES"), para("TRANSACTIONCATEGORY"))
        End If
        If para.ContainsKey("ActualFinishDate") Then '完成
            sb.AppendFormat(" STATUS='2',ActualFinishDate=sysdate", "")
        End If
        If para.ContainsKey("RECIEVEEMPLOYEEID") Then '接收
            sb.AppendFormat("STATUS='1',RECIEVEDATE=sysdate,RECIEVEEMPLOYEEID='{0}'", para("RECIEVEEMPLOYEEID"))
        End If
        If para.ContainsKey("STATUS") Then '更新其他状态
            sb.AppendFormat("STATUS='{0}' ", para("STATUS"))
        End If
        sb.AppendFormat(" WHERE TRANSACTIONID IN ('{0}')", para("TRANSACTIONID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function



End Class
